﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Web;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel; 
namespace Ls.Utils
{
    /// <summary>
    /// Excel 导出
    /// </summary>
    public static class NPOIHelper
    {
        public static byte[] ExportXLSX(List<dynamic> data, Dictionary<string,string> columns, string name)
        {
            List<ExcelColumn> list = new List<ExcelColumn>();
            foreach (string key in columns.Keys)
            {
                list.Add(new ExcelColumn { field= key, header= columns[key] });
            }
            return ExportXLSX(data, list, name);
        } 
        public static byte[] ExportXLSX(List<dynamic> data, List<ExcelColumn> columns, string name)
        {
            XSSFWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet(name); 
            int HeaderRowsMax = 1; 
            if (columns.Where(c => c.columns != null && c.columns.Count > 0).Count() > 0)
            {
                HeaderRowsMax = 2;
            }
            IRow headerRow1 = sheet.CreateRow(0);
            int column = 0;
            var headFont = workbook.CreateFont();
            headFont.IsBold = true; //字体为粗体
            var headStyle = workbook.CreateCellStyle();
            headStyle.Alignment = HorizontalAlignment.Center;
            headStyle.VerticalAlignment = VerticalAlignment.Center;
            headStyle.SetFont(headFont);
            IRow headerRow2 = null;
            if (HeaderRowsMax == 2)  headerRow2 = sheet.CreateRow(1); 
            foreach (var col in columns)
            {
                var cel = headerRow1.CreateCell(column);
                cel.SetCellValue(col.header);
                cel.SetCellType(CellType.String);
                cel.CellStyle = headStyle; 
                if (col.columns != null && col.columns.Count > 0)
                {
                    if (col.columns.Count > 1)
                    {
                        sheet.AddMergedRegion(new CellRangeAddress(0,0, column, column+ col.columns.Count));
                    }
                    foreach (var col2 in col.columns)
                    {
                        var cel2 = headerRow2.CreateCell(column);
                        cel2.SetCellValue(col2.header);
                        cel.SetCellType(CellType.String);
                        cel.CellStyle = headStyle; 
                        column++;
                    }
                }
                else
                {
                    if (HeaderRowsMax > 2)
                    {
                        sheet.AddMergedRegion(new CellRangeAddress(0,1, column, column)); // 合并单元格 
                    }
                    column++;
                }
            }
            if (data.Any())
            {
                for (int i = 0; i < data.Count; i++)
                {
                    IRow dataRow = sheet.CreateRow(i+ HeaderRowsMax); // 创建数据行
                    column = 0;
                    var dic = (data[i] as IDictionary<string, object>);
                    foreach (var col in columns)
                    {

                        if (col.columns != null && col.columns.Count > 0)
                        {
                            foreach (var col2 in col.columns)
                            {
                                ICell cel = dataRow.CreateCell(column);
                                object value;
                                if (!dic.TryGetValue(col2.field, out value))
                                    value = "";
                                if (value != null)
                                {
                                    string tmp = value.ToString();
                                    value = tmp.Replace("<br>", "\r\n");
                                }
                                else
                                    value = ""; 
                                cel.SetCellValue(value.ToString()); 
                                column++;
                            }
                        }
                        else
                        {
                            ICell cel = dataRow.CreateCell(column);
                            object value;
                            if (!dic.TryGetValue(col.field, out value))
                                value = "";
                            if (value != null)
                            {
                                string tmp = value.ToString();
                                value = tmp.Replace("<br>", "\r\n");
                            }
                            else
                                value = "";
                            cel.SetCellValue(value.ToString());
                            column++;
                        }
                    }
                }
            }
            MemoryStream ms = new MemoryStream();
            workbook.Write(ms);
            ms.Flush();
             
            return ms.ToArray(); 
        }
        public static byte[] ExportXLSX(DataTable dt, List<ExcelColumn> columns, string name)
        {
            XSSFWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet(name);
            int HeaderRowsMax = 1;
            if (columns.Where(c => c.columns != null && c.columns.Count > 0).Count() > 0)
            {
                HeaderRowsMax = 2;
            }
            IRow headerRow1 = sheet.CreateRow(0);
            int column = 0;
            var headFont = workbook.CreateFont();
            headFont.IsBold = true; //字体为粗体
            var headStyle = workbook.CreateCellStyle();
            headStyle.Alignment = HorizontalAlignment.Center;
            headStyle.VerticalAlignment = VerticalAlignment.Center;
            headStyle.SetFont(headFont);
            IRow headerRow2 = null;
            if (HeaderRowsMax == 2) headerRow2 = sheet.CreateRow(1);
            foreach (var col in columns)
            {
                var cel = headerRow1.CreateCell(column);
                cel.SetCellValue(col.header);
                cel.SetCellType(CellType.String);
                cel.CellStyle = headStyle;
                if (col.columns != null && col.columns.Count > 0)
                {
                    if (col.columns.Count > 1)
                    {
                        sheet.AddMergedRegion(new CellRangeAddress(0, 0, column, column + col.columns.Count));
                    }
                    foreach (var col2 in col.columns)
                    {
                        var cel2 = headerRow2.CreateCell(column);
                        cel2.SetCellValue(col2.header);
                        cel.SetCellType(CellType.String);
                        cel.CellStyle = headStyle;
                        column++;
                    }
                }
                else
                {
                    if (HeaderRowsMax > 2)
                    {
                        sheet.AddMergedRegion(new CellRangeAddress(0, 1, column, column)); // 合并单元格 
                    }
                    column++;
                }
            }
            if (dt != null && dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    IRow dataRow = sheet.CreateRow(i + HeaderRowsMax); // 创建数据行
                    var dic = dt.Rows[i];
                    foreach (var col in columns)
                    {

                        if (col.columns != null && col.columns.Count > 0)
                        {
                            foreach (var col2 in col.columns)
                            {
                                ICell cel = dataRow.CreateCell(column);
                                string value = dic[col2.field].ToString();

                                if (value != null)
                                {
                                    string tmp = value.ToString();
                                    value = tmp.Replace("<br>", "\r\n");
                                }
                                cel.SetCellValue(value.ToString());
                                column++;
                            }
                        }
                        else
                        {
                            ICell cel = dataRow.CreateCell(column);
                            object value = dic[col.field].ToString();
                            if (value != null)
                            {
                                string tmp = value.ToString();
                                value = tmp.Replace("<br>", "\r\n");
                            }
                            cel.SetCellValue(value.ToString());
                            column++;
                        }
                    }
                }
            }

            MemoryStream ms = new MemoryStream();
            workbook.Write(ms);
            ms.Flush(); 
            workbook = null; 
            return ms.ToArray(); 
        }
        /// <summary>  
        /// 由DataSet导出Excel  
        /// </summary>  
        /// <param name="sourceTable">要导出数据的DataTable</param>     
        /// <param name="sheetName">工作表名称</param>  
        /// <returns>Excel工作表</returns>     
        private static Stream ExportDataSetToExcel(DataSet sourceDs)
        {
            XSSFWorkbook workbook = new XSSFWorkbook();
            MemoryStream ms = new MemoryStream();

            for (int i = 0; i < sourceDs.Tables.Count; i++)
            {
                ISheet sheet =  workbook.CreateSheet(sourceDs.Tables[i].TableName);
                IRow headerRow = sheet.CreateRow(0);
                // handling header.             
                foreach (DataColumn column in sourceDs.Tables[i].Columns)
                    headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                // handling value.             
                int rowIndex = 1;
                foreach (DataRow row in sourceDs.Tables[i].Rows)
                {
                    IRow dataRow = sheet.CreateRow(rowIndex);
                    foreach (DataColumn column in sourceDs.Tables[i].Columns)
                    {
                        dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                    }
                    rowIndex++;
                }
            }
            workbook.Write(ms);
            ms.Flush(); 
            workbook = null;
            return ms;
        }

        /// <summary>  
        /// 由DataTable导出Excel  
        /// </summary>  
        /// <param name="sourceTable">要导出数据的DataTable</param>  
        /// <returns>Excel工作表</returns>     
        private static Stream ExportDataTableToExcel(DataTable sourceTable)
        {
            XSSFWorkbook workbook = new XSSFWorkbook();
            MemoryStream ms = new MemoryStream();
            ISheet sheet =  workbook.CreateSheet(sourceTable.TableName);
            IRow headerRow = sheet.CreateRow(0);
            // handling header.       
            foreach (DataColumn column in sourceTable.Columns)
                headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
            // handling value.       
            int rowIndex = 1;
            foreach (DataRow row in sourceTable.Rows)
            {
                IRow dataRow =  sheet.CreateRow(rowIndex);
                foreach (DataColumn column in sourceTable.Columns)
                {
                    dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                }
                rowIndex++;
            }
            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;
            sheet = null;
            headerRow = null;
            workbook = null;
            return ms;
        }

        /// <summary>  
        /// 传入行数超过65325的Table，返回DataSet  
        /// </summary>  
        /// <param name="dt"></param>  
        /// <returns></returns>  
        public static DataSet GetdtGroup(DataTable dt)
        {
            string tablename = dt.TableName;

            DataSet ds = new DataSet();
            ds.Tables.Add(dt);

            double n = dt.Rows.Count / 65325;

            //创建表  
            for (int i = 1; i < n; i++)
            {
                DataTable dtAdd = dt.Clone();
                dtAdd.TableName = tablename + "_" + i.ToString();
                ds.Tables.Add(dtAdd);
            }

            //分解数据  
            for (int i = 1; i < ds.Tables.Count; i++)
            {
                //新表行数达到最大 或 基表数量不足  
                while (ds.Tables[i].Rows.Count != 65325 && ds.Tables[0].Rows.Count != 65325)
                {
                    ds.Tables[i].Rows.Add(ds.Tables[0].Rows[65325].ItemArray);
                    ds.Tables[0].Rows.RemoveAt(65325);

                }
            }

            return ds;
        }

        /// <summary>  
        /// 由DataTable导出Excel  
        /// </summary>  
        /// <param name="sourceTable">要导出数据的DataTable</param>  
        /// <param name="fileName">指定Excel工作表名称</param>  
        /// <returns>Excel工作表</returns>  
        public static void ExportDataTableToExcelModel(DataTable sourceTable, string modelpath, string modelName, string fileName, string sheetName)
        {
            int rowIndex = 2;//从第二行开始，因为前两行是模板里面的内容  
            int colIndex = 0;
            FileStream file = new FileStream(modelpath + modelName + ".xls", FileMode.Open, FileAccess.Read);//读入excel模板  
            HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
            HSSFSheet sheet1 = (HSSFSheet)hssfworkbook.GetSheet("Sheet1");
            sheet1.GetRow(0).GetCell(0).SetCellValue("excelTitle");      //设置表头  
            foreach (DataRow row in sourceTable.Rows)
            {   //双循环写入sourceTable中的数据  
                rowIndex++;
                colIndex = 0;
                HSSFRow xlsrow = (HSSFRow)sheet1.CreateRow(rowIndex);
                foreach (DataColumn col in sourceTable.Columns)
                {
                    xlsrow.CreateCell(colIndex).SetCellValue(row[col.ColumnName].ToString());
                    colIndex++;
                }
            }
            sheet1.ForceFormulaRecalculation = true;
            FileStream fileS = new FileStream(modelpath + fileName + ".xls", FileMode.Create);//保存  
            hssfworkbook.Write(fileS);
            fileS.Close();
            file.Close();
        }

        /// <summary>
        /// 将excel中的数据导入到DataTable中
        /// </summary>
        /// <param name="sheetName">excel工作薄sheet的名称</param>
        /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
        /// <returns>返回的DataTable</returns>
        public static DataTable ExcelToDataTable(string fileName, bool isFirstRowColumn)
        {
            ISheet sheet = null;
            DataTable data = new DataTable();
            int startRow = 0;
            IWorkbook workbook = null;
            try
            {
                var fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
                if (fileName.ToLower().IndexOf(".xlsx") > 0) // 2007版本
                    workbook = new XSSFWorkbook(fs);
                else if (fileName.ToLower().IndexOf(".xls") > 0) // 2003版本
                    workbook = new HSSFWorkbook(fs); 
                return Excel2Datatable(isFirstRowColumn, ref sheet, data, ref startRow, workbook);
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: " + ex.Message);
                return null;
            }
        }
        /// <summary>
        /// 将excel 2003中的数据导入到DataTable中
        /// </summary>
        /// <param name="fs">excel的stream</param>
        /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
        /// <returns>返回的DataTable</returns>
        public static DataTable ExcelToDataTable(Stream fs, bool isFirstRowColumn,string fileName)
        {
            ISheet sheet = null;
            DataTable data = new DataTable();
            int startRow = 0;
            IWorkbook workbook = null;
            try
            {
                if (fileName.ToLower().IndexOf(".xlsx") > 0) // 2007版本
                    workbook = new XSSFWorkbook(fs);
                else if (fileName.ToLower().IndexOf(".xls") > 0) // 2003版本
                    workbook = new HSSFWorkbook(fs);
                return Excel2Datatable(isFirstRowColumn, ref sheet, data, ref startRow, workbook);
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: " + ex.Message);
                return null;
            }
        }

        /// <summary>
        /// 将excel 2003中的数据导入到DataTable中
        /// </summary>
        /// <param name="fs">excel的stream</param>
        /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
        /// <returns>返回的DataTable</returns>
        public static DataTable XlsToDataTable(Stream fs, bool isFirstRowColumn)
        {
            ISheet sheet = null;
            DataTable data = new DataTable();
            int startRow = 0;
            IWorkbook workbook = null;
            try
            {
                workbook = new HSSFWorkbook(fs);
                return Excel2Datatable(isFirstRowColumn, ref sheet, data, ref startRow, workbook); 
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: " + ex.Message);
                return null;
            }
        }


        /// <summary>
        /// 将excel 2007中的数据导入到DataTable中
        /// </summary>
        /// <param name="fs">excel的stream</param>
        /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
        /// <returns>返回的DataTable</returns>
        public static DataTable XlsxToDataTable(Stream fs, bool isFirstRowColumn)
        {
            ISheet sheet = null;
            DataTable data = new DataTable();
            int startRow = 0;
            IWorkbook workbook = null;
            try
            {
                workbook = new XSSFWorkbook(fs);
                return Excel2Datatable(isFirstRowColumn, ref sheet, data, ref startRow, workbook);
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: " + ex.Message);
                return null;
            }
        }

        private static DataTable Excel2Datatable(bool isFirstRowColumn, ref ISheet sheet, DataTable data, ref int startRow, IWorkbook workbook)
        {
            if (workbook == null)
            {
                return null;
            }

            sheet = workbook.GetSheetAt(0);
            if (sheet != null)
            {
                IRow firstRow = sheet.GetRow(0);
                int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数

                if (isFirstRowColumn)
                {
                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                    {
                        ICell cell = firstRow.GetCell(i);
                        if (cell != null)
                        {
                            string cellValue = cell.StringCellValue;
                            if (cellValue != null)
                            {
                                DataColumn column = new DataColumn(cellValue);
                                data.Columns.Add(column);
                            }
                        }
                    }
                    startRow = sheet.FirstRowNum + 1;
                }
                else
                {
                    startRow = sheet.FirstRowNum;
                }

                //最后一列的标号
                int rowCount = sheet.LastRowNum;
                for (int i = startRow; i <= rowCount; ++i)
                {
                    IRow row = sheet.GetRow(i);
                    if (row == null) continue; //没有数据的行默认是null　　　　　　　

                    DataRow dataRow = data.NewRow();
                    for (int j = row.FirstCellNum; j < cellCount; ++j)
                    {
                        if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
                            dataRow[j] = row.GetCell(j).ToString();
                    }
                    data.Rows.Add(dataRow);
                }
            }
            return data;
        }
    }

    public class ExcelColumn
    {
        public string header { get; set; }
        public string field { get; set; }

        /// <summary>
        /// 计算公式
        /// </summary>
        public string Formula { get; set; }

        /// <summary>
        /// 输出格式
        /// </summary>
        public string Format { get; set; }
        public List<ExcelColumn> columns { get; set; }
    }
}
